<!doctype html public "-//w3c//dtd html 4.0 transitional//en">


<!-- WARNING! This file is generated. -->
<!-- To alter documentation, edit files in src directory -->


<html><head>
<title>Test an Entire Package API</title>
<link rel="stylesheet" href="utplsql.css" content="text/css">
<meta name="keywords" content="utPLSQL, PL\SQL, Unit Testing, Framework, Oracle"/>
<meta name="description" content="Unit Testing PL\SQL"/>
<meta name="title" content="Test an Entire Package API"/>
<meta name="author" content="Steven Feuerstein, Chris Rimmer, Patrick Barel"/>
<meta name="copyright" content="(C) 2000-2005 Steven Feuerstein, Chris Rimmer, Patrick Barel"/>
</head><body>
<div class="purple_bar"><a href="index.html"><img src="utplsql.jpg" border=0></a></div>
<p>[ <A href="index.html">Home</A>
 | <A href="started.html">Getting Started</A>
 | <A href="buildpack.html">Build Test Packages</A>
 | <A href="examples.html">Examples</A>
 | <A href="userguide.html">User Guide</A>
 | <A href="release.html">Release Notes</A>
 | <A href="map.html">Document Map</A> ]</p>
<p><A href="testfunc.html">&lt; Previous Section: Test a Function</A> | <A href="samepack.html">Next Section: Put Test Code in Same Package &gt;</A></p>
<!-- Begin utPLSQL Body -->
<!-- $Id: testapi.html,v 1.3 2002/07/25 12:18:18 chrisrimmer Exp $ -->
<h1>
Test an Entire Package API</h1>

<p>Most packages consist of lots more than a single program, and you will
generally want to test each and every of the programs listed in the package
specification. When you generate a test package with <a href="utgen.html">utGen</a>,
it will produce a template unit test procedure for each program in the
package specification. You will then need to modify each of these programs.
<p>One example of this more complex package structure is the table encapsulation
package. This kind of package establishes a layer of code and therefore
control between application requirements and underlying data structures.
While the building of such a layer is uncommon in the world of PL/SQL developers,
it is strongly recommended practice. A variety of tools, in fact, offer
automated table encapsulation package generation, including <a href="http://www.oracle.com">Oracle
Designer</a>, <a href="http://www.revealnet.com">RevealNet</a>'s PL/Generator
and a variety of IDE (integrated development environment) tools.
<p>Suppose, then, that I used PL/Generator to generate a table encapsulation
package for the employee table. It would look like the code found in te_employee.pks
and te_employee.pkb<a href="#footnote">(1)</a> (being rather
lengthy, we will not reproduce it in the documentation. If you take a look,
you will see that their are dozens of programs in the API, which means
that you would have lots of work to do in building your unit test cases.
In addition, many of the programs will be performing DML operations (updating,
deleting, inserting). How you can easily and dependably test those programs?
<p>When you are dealing with lots of programs that have a uniform structure
and behavior (which <i>should</i> be the case if you are building table
API packages), then you should look for ways to <i>generate</i>, rather
than write manually, your test package. utGen cannot do this generation
work for you, since the logic in your encapsulation package is specific
to your environment.
<p>You can, instead, build your own custom generator or use an existing
generator that is sufficiently flexible to meet your needs. The original
creator of utPLSQL, <a href="http://www.stevenfeuerstein.com">Steven Feuerstein</a>,
has also been working on generator utilities for a number of years. One
of these utilities, currently "code named" GenX, came in very handy for
creating a test package for his PL/Generator-generated encapsulation packages.
<p>Using CGML (Code Generation Markup Language), Steven created a template 
(See te_utpkg.gdr in the Examples directory of the utPLSQL distribution)
that reads information from the data dictionary and defines the setup,
teardown and at least a good starting point for the unit test procedures.
Here is the template logic for the setup procedure:
<pre>   PROCEDURE {utprefix}setup
   IS
   BEGIN
      -- Clean start
      {utprefix}teardown;
[ASIS]   
      -- Generic copy of base table for testing 
      EXECUTE IMMEDIATE 
         'CREATE TABLE {tabprefix}[objname] AS
            SELECT * FROM [objname]';
            
[ENDASIS]   
   [FOREACH]prog
   [IF]{allprogs}[EQ]Y[OR][progname][LIKE]UPD%[OR][progname][LIKE]INS%[OR][progname][LIKE]DEL%
      -- Create copy of base table for this unit test.
      EXECUTE IMMEDIATE 
         'CREATE TABLE ^{progtab}^ AS
[ASIS]   
            SELECT * FROM [objname]';
[ENDASIS]   
            
   [ENDIF]
   [ENDFOREACH]
   END;</pre>
You are not, of course, expected to understand all the logic and syntax
in this fragment. If you are interested in pursuing these sorts of genreation
opportunities and would like to check out GenX, drop a note to <a href="mailto:steven@stevenfeuerstein.com">Steven
Feuerstein</a>.
<p>Here is a portion of the generated logic (found in ut_te_employee.pks
and ut_te_employee.pkb"<a href="#footnote">(1)</a>), the
program that tests the delete operation in the encapsulation package:
<pre>   PROCEDURE ut_del1
   IS
      fdbk PLS_INTEGER;
   BEGIN
      /* Delete that finds now rows. */

      EXECUTE IMMEDIATE '
      DELETE FROM ut_DEL1
       WHERE employee_id = -1
      ';
      te_employee.del (-1, rowcount_out =&gt; fdbk);
      -- Test results
      utassert.eqtable ('Delete rows', 'EMPLOYEE', 'ut_DEL1');
      /* Successful delete */

      EXECUTE IMMEDIATE '
      DELETE FROM ut_DEL1
       WHERE employee_id between 7800 and 7899
      ';

      FOR rec IN (SELECT *
                    FROM employee
                   WHERE employee_id BETWEEN 7800 AND 7899)
      LOOP
         te_employee.del (
            rec.employee_id,
            rowcount_out =&gt; fdbk
         );
      END LOOP;

      -- Test results
      utassert.eqtable ('Delete rows', 'EMPLOYEE', 'ut_DEL1');
      ROLLBACK;
   EXCEPTION
      WHEN OTHERS
      THEN
         utassert.this (
            'DEL1 exception ' || SQLERRM,
            SQLCODE = 0
         );
   END;</pre>
In this procedure, I test for two scenarios: a delete that removes zero
rows and a delete that removes a specific set of rows. In both cases, I
perform the explicit (non-encapsulated)  DML logic against a <i>copy</i>
of the actual table (this copy is created in the <a href="#SetupAPI">setup
procedure</a>; that is the reason I use dynamic SQL to refer to this table
-- it doesn't exist when the package is compiled!). Then I do the (hopefully)
same operation by using the API program. Finally, I call the appropriate
utAssert assertion program to compare the results -- and at the end of
the procedure issue a ROLLBACK so that my "source" table (employee, in
this case), i set back to the original data state. Notice that I also put
an assertion program in the exception section to trap any errors and flag
it as a failed test.
<p>That should give you a good feel for the kind of code you might write
to test a table encapsulation package. The next two sections show you how
I used the setup and teardown procedures to manage the data structures
I use in my tests.
<h3>
<a name="SetupAPI"></a>Set Up Data Structures</h3>
As I contemplated how best to test these large packages, I revisited some
of my testing principles and found one to be of particular importance:

<p><b>Build isolated tests.</b>

<p>This principle is important because it allows you to run one, all or
a subset of your tests without having to worry about the impact or dependencies
on the other tests. And test isolation is <i>particularly </i>important
when testing DML operations. The way to validate a successful DML operation
is by analyzing the contents of the "source" table against a "test" table.
If all the tests modify the same test table, ti will be very difficult
if not impossible to verify success or notice failure.
<p>So I decided that the best way to run my unit tests for DML operations
was to create a separate test table for each unit test. As a consequence,
my setup procedure for the te_employee package looks like this:
(See ut_te_employee.pkb in the Examples directory of the utPLSQL distribution)
<pre>   PROCEDURE ut_setup
   IS
   BEGIN
      ut_teardown;
      EXECUTE IMMEDIATE 'CREATE TABLE ut_employee AS
            SELECT * FROM employee';
      EXECUTE IMMEDIATE 'CREATE TABLE ut_DEL1 AS
            SELECT * FROM employee';
      EXECUTE IMMEDIATE 'CREATE TABLE ut_DELBY_EMP_DEPT_LOOKUP AS
            SELECT * FROM employee';
      EXECUTE IMMEDIATE 'CREATE TABLE ut_DELBY_EMP_JOB_LOOKUP AS
            SELECT * FROM employee';
      EXECUTE IMMEDIATE 'CREATE TABLE ut_DELBY_EMP_MGR_LOOKUP AS
            SELECT * FROM employee';
      EXECUTE IMMEDIATE 'CREATE TABLE ut_INS1 AS
            SELECT * FROM employee';
      EXECUTE IMMEDIATE 'CREATE TABLE ut_UPD1 AS
            SELECT * FROM employee';
      EXECUTE IMMEDIATE 'CREATE TABLE ut_UPD$HIRE_DATE1 AS   
            SELECT * FROM employee';
      EXECUTE IMMEDIATE 'CREATE TABLE ut_UPD$SALARY1 AS
            SELECT * FROM employee';
   END;</pre>
I first remove all my data structures using the teardown procedure to make
sure I have a clean start. Then I use dynamic SQL (the Oracle8i version)
to create all my tables. I must rely on dynamic SQL because PL/SQL does
not yet support native DDL statements, such as CREATE TABLE.
<p>Then I am set to test.
<h3>
<a name="TeardownAPI"></a>Tear Down Data Structures</h3>
Well, if I am going to create a whole bunch of data structures to run my
tests, I had better get rid of those structures when I am done. Here is
the teardown program I generated for the te_employee package:
<pre>   PROCEDURE ut_teardown
   IS
   BEGIN
      BEGIN
         EXECUTE IMMEDIATE 'DROP TABLE ut_employee';
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;

      BEGIN
         EXECUTE IMMEDIATE 'DROP TABLE ut_DEL1';
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;

      BEGIN
         EXECUTE IMMEDIATE 'DROP TABLE ut_DELBY_EMP_DEPT_LOOKUP';
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;

      BEGIN
         EXECUTE IMMEDIATE 'DROP TABLE ut_DELBY_EMP_JOB_LOOKUP';
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;

      BEGIN
         EXECUTE IMMEDIATE 'DROP TABLE ut_DELBY_EMP_MGR_LOOKUP';
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;

      BEGIN
         EXECUTE IMMEDIATE 'DROP TABLE ut_INS1';
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;

      BEGIN
         EXECUTE IMMEDIATE 'DROP TABLE ut_UPD1';
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;

      BEGIN
         EXECUTE IMMEDIATE 'DROP TABLE ut_UPD$HIRE_DATE1';
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;

      BEGIN
         EXECUTE IMMEDIATE 'DROP TABLE ut_UPD$SALARY1';
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;

   END;</pre>
Again, I use dynamic SQL, but enclose each DROP TABLE statement inside
its own exception section so that if for any reason the DROP fails, I continue
on in an attempt to get as much done as possible.
<hr>
<h3><a name="footnote">Footnotes</a></h3>
1. These files are in the Examples directory of the utPLSQL distribution.
<!-- End utPLSQL Body -->
<p><A href="testfunc.html">&lt; Previous Section: Test a Function</A> | <A href="samepack.html">Next Section: Put Test Code in Same Package &gt;</A></p>
<div class="purple_bar"><a href="index.html"><img src="utplsql.jpg" border=0></a></div>
<p class="copyright">Copyright (C) 2000-2005 <A href="mailto:steven@stevenfeuerstein.com">Steven Feuerstein<A>, <A href="mailto:c@24.org.uk">Chris Rimmer<A>, <A href="mailto:pbarel@vda.nl">Patrick Barel<A> All rights reserved</p>
</body></html>